package jmind.core.poi;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import jmind.core.support.BeanProperty;
import jmind.base.util.DataUtil;
import jmind.base.util.DateUtil;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.google.common.collect.Lists;

/**
 * 利用开源组件POI3.0.2动态导出EXCEL文档 转载时请保留以下信息，注明出处！
 * 07后新版本  Xlsx excel
 * @author leno
 * @version v1.0
 *
 *            应用泛型，代表任意一个符合javabean风格的类
 *            注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 *            byte[]表jpg格式的图片数据
 */
public class XlsxExcel extends Excel {

    final static XlsxExcel INSTANCE = new XlsxExcel();

    @Override
    public Workbook exportExcel(String sheetName, List<?> datas) {
        // 创建工作簿（Excel文件）
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 新增的四句话，设置CELL格式为文本格式
        XSSFCellStyle textCellStyle = workbook.createCellStyle();
        XSSFDataFormat format = workbook.createDataFormat();
        textCellStyle.setDataFormat(format.getFormat("@"));
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 创建Sheet页的文件体
        for (int rownum = 0; rownum < datas.size(); rownum++) {
            XSSFRow row = sheet.createRow(rownum);
            XSSFCell cell = row.createCell(0);
            cell.setCellStyle(textCellStyle);
            cell.setCellValue(datas.get(rownum).toString());
        }

        return workbook;
    }




    /**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     * 
     * @param sheetName
     *            表格标题名
     * @param headers
     *            表格属性列名数组
     * @param dataset  所有getXxx() 方法都会取到  这里bean 的所有属性都会列出
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param dateFormart
     *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */

    public <T> XSSFWorkbook exportExcel(String sheetName, List<T> dataset, String[] headers, String dateFormart) {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式

        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        XSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

        // 声明一个画图的顶级管理器
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        XSSFComment comment = patriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new XSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        //    comment.setAuthor("leno");


        BeanInfo beanInfo=null;
        PropertyDescriptor[] descriptors=null;
        try {
             beanInfo = Introspector.getBeanInfo(dataset.get(0).getClass());
            PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
            descriptors=new PropertyDescriptor[propertyDescriptors.length-1];
             int i=0;
             for(PropertyDescriptor pd: propertyDescriptors){
                 if("class".equalsIgnoreCase(pd.getName()))
                     continue;
                 descriptors[i++]=pd;
             }
        } catch (IntrospectionException e) {
            e.printStackTrace();
            return null;
        }


        if (headers == null) {
            headers = new String[descriptors.length];
            for (int i = 0; i < descriptors.length; i++) {
                headers[i] = descriptors[i].getName();
            }
        }
        // 产生表格标题行
        XSSFRow row0 = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            XSSFCell cell = row0.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行

        for (int index = 0; index < dataset.size(); index++) {
            // 因为第一行已经创建
            XSSFRow row = sheet.createRow(index + 1);
            T t = dataset.get(index);
            // 利用内省模型，可以获取父类的get方法
            for (int i = 0; i < descriptors.length; i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                try {
                    Method getMethod = descriptors[i].getReadMethod();
                    Object value = getMethod.invoke(t);
                    if (value == null) {
                        cell.setCellValue("");

                    } else {
                        // 判断值的类型后进行强制类型转换
                        String textValue = null;

                        if (value instanceof Date) {
                            Date date = (Date) value;
                            textValue = DateUtil.format(date,dateFormart);
                        } else if (value instanceof byte[]) { // 图片处理
                            // 有图片时，设置行高为60px;
                            row.setHeightInPoints(60);
                            // 设置图片所在列宽度为80px,注意这里单位的一个换算
                            sheet.setColumnWidth(i, (short) (35.7 * 80));
                            // sheet.autoSizeColumn(i);
                            byte[] bsValue = (byte[]) value;
                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index,
                                    (short) 6, index);
                           // anchor.setAnchorType(2);

                            patriarch.createPicture(anchor,
                                    workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                        } else {
                            // 其它数据类型都当作字符串简单处理
                            textValue = value.toString();
                        }
                        // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
                        if (textValue != null) {
                            XSSFRichTextString richString = new XSSFRichTextString(textValue);
                            XSSFFont font3 = workbook.createFont();
                            font3.setColor(HSSFColor.BLUE.index);
                            richString.applyFont(font3);
                            cell.setCellValue(richString);

                        }
                    }

                } catch (Exception e) {
                    e.printStackTrace();

                }
            }

        }

        return workbook;

    }

    @Override
    public <T> Workbook exportExcel(String sheetName, List<T> dataset, String[] headers, String[] methods,String dateFormart) {
        // 创建工作簿（Excel文件）
        XSSFWorkbook workbook = new XSSFWorkbook();
        if (DataUtil.isEmpty(dataset))
            return workbook;
        // 新增的四句话，设置CELL格式为文本格式
        XSSFCellStyle textCellStyle = workbook.createCellStyle();
        XSSFDataFormat format = workbook.createDataFormat();
        textCellStyle.setDataFormat(format.getFormat("@"));
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 创建Sheet页的文件头（第一行）
        XSSFRow firstRow = sheet.createRow(0); // 创建该页的一行
        for (int i = 0; i < headers.length; i++) {
            XSSFCell cell = firstRow.createCell(i); // 创建该行的一列
            cell.setCellValue(headers[i]);

        }
        // 创建Sheet页的文件体（后续行）
        Class<? extends Object> target = dataset.get(0).getClass();
        for (int rownum = 0; rownum < dataset.size(); rownum++) {
            Object obj = dataset.get(rownum);
            XSSFRow row = sheet.createRow(rownum + 1);
            for (int columnIndex = 0; columnIndex < methods.length; columnIndex++) {
                try {
                    XSSFCell cell = row.createCell(columnIndex);
                    cell.setCellStyle(textCellStyle);
                    Method method = target.getMethod(methods[columnIndex]);
                    Object value = method.invoke(obj);
                    if (value == null) {
                        continue;
                    }
                    if (value instanceof Date) {
                        cell.setCellValue(DateUtil.format((Date) value, dateFormart));
                    } else {
                        cell.setCellValue(value.toString());
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }
        return workbook;
    }

    @Override
    public Workbook exportMapExcel(String sheetName, List<Map<String, String>> objList) {
        if (DataUtil.isEmpty(objList))
            return null;
        // 创建工作簿（Excel文件）
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建Excel工作簿的第一个Sheet页
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 创建Sheet页的文件体
        for (int i = 0; i < objList.size(); i++) {
            Map<String, String> map = objList.get(i);
            XSSFRow row = sheet.createRow(i);
            int j = 0;
            for (String k : map.values()) {
                XSSFCell cell = row.createCell(j++);
                cell.setCellValue(k);
            }
        }
        return workbook;
    }

    @Override
    public List<String> excel2StrList(InputStream in) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = workbook.getSheetAt(0);
            List<String> list = Lists.newArrayListWithExpectedSize(sheet.getPhysicalNumberOfRows());
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheet.getRow(i);
                try {
                    XSSFCell cell = row.getCell(0);
                    // 全部当中String 处理， getStringCellValue 时才不报错
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    String value = cell.getStringCellValue();
                    if (!DataUtil.isEmpty(value))
                        list.add(value);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            return list;
        } catch (IOException e1) {
            e1.printStackTrace();
            return Collections.emptyList();
        }

    }

    @Override
    public <T> List<T> excel2List(InputStream in, Class<T> claz, String[] fields, int start) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = workbook.getSheetAt(0);
            List<T> list = Lists.newArrayListWithExpectedSize(sheet.getPhysicalNumberOfRows());
            if (fields == null) { // 没定义 字段，从excel读出
                XSSFRow row = sheet.getRow(0);
                fields = new String[row.getPhysicalNumberOfCells()];
                for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                    fields[i] = row.getCell(i).getStringCellValue();
                }
            }

            for (int i = start; i < sheet.getPhysicalNumberOfRows(); i++) {
                BeanProperty<T> bean = new BeanProperty<T>(claz);
                XSSFRow row = sheet.getRow(i);
                for (int j = 0; j < fields.length; j++) {
                    if (DataUtil.isEmpty(fields[j]))
                        continue;
                    try {
                        XSSFCell cell = row.getCell(j);
                        // 全部当中String 处理， getStringCellValue 时才不报错
                        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                        String value = cell.getStringCellValue();
                        if (value != null) {
                            bean.setProperty(fields[j], value.trim());
                        }
                        //   BeanUtils.setProperty(obj, fields[j], cell.getStringCellValue());

                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                list.add(bean.get());
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return Collections.emptyList();
        }

    }

    @Override
    public List<Map<String, String>> excel2Map(InputStream in, String[] strKey) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            // 读取第一章表格内容
            XSSFSheet sheet = workbook.getSheetAt(0);
            List<Map<String, String>> list = Lists.newArrayListWithExpectedSize(sheet.getPhysicalNumberOfRows());
            // 定义 row、cell
            XSSFRow row;
            XSSFCell cell;
            // 循环输出表格中的内容
            for (int rowIndex = sheet.getFirstRowNum() + 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
                row = sheet.getRow(rowIndex);
                if (row == null || row.getFirstCellNum() < 0)
                    break;
                Map<String, String> map = new LinkedHashMap<String, String>();
                for (int cellIndex = row.getFirstCellNum(); cellIndex < strKey.length; cellIndex++) {
                    // 通过 row.getCell(j).toString() 获取单元格内容，
                    String key = strKey[cellIndex];
                    cell = row.getCell(cellIndex);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String value = cell.getStringCellValue();
                    map.put(key, value);
                }
                list.add(map);
            }
            return list;

        } catch (IOException e) {
            e.printStackTrace();
            return Collections.emptyList();
        }

    }

}